package biz;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;

import tree.ItemTree;

import db.*;
import bean.*;

/**
 * 词条操作类
 * @author Shi Mengjie
 * @version 1.0
 */
public class ItemBiz implements Search {
	
	public static Item getItemByIid(int iid){
		Item item = new Item();
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		String sqlQuery = "select * from items where iid = ?";
		ResultSet rs = null;
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, iid);
			rs = prepSt.executeQuery();
			while (rs.next()){
				item.setIid(rs.getInt("iid"));
				item.setIname(rs.getString("iname"));
				item.setCurvid(rs.getInt("curvid"));
				item.setLevel(rs.getInt("level"));
				item.setParent(rs.getInt("parent"));
				item.setRoot(rs.getInt("root"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		if (item.isNull()) return null;
		else return item;
	}
	
	/**
	 * 同根的item树在同一层中不能有同名节点，故可以唯一确定item
	 * @param iname
	 * @param level
	 * @param root
	 * @return Item
	 */
	public static Item getItemByNameLevelRoot(String iname,int level,int root){
		Item item = new Item();
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		String sqlQuery = "select * from items where iname = ? and level = ? and root = ?";
		ResultSet rs = null;
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setString(1, iname);
			prepSt.setInt(2, level);
			prepSt.setInt(3, root);
			rs = prepSt.executeQuery();
			while (rs.next()){
				item.setIid(rs.getInt("iid"));
				item.setIname(rs.getString("iname"));
				item.setCurvid(rs.getInt("curvid"));
				item.setLevel(rs.getInt("level"));
				item.setParent(rs.getInt("parent"));
				item.setRoot(rs.getInt("root"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		System.out.println(item.getIid());
		if (item.isNull()) return null;
		else return item;
	}
	
	/**
	 * 通过iname获取根Item的iid
	 * @param iname
	 * @return boolean
	 */
	public static int getRootIidByName(String iname){
		Item item = new Item();
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		String sqlQuery = "select * from items where iname = ? and level = 0";
		ResultSet rs = null;
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setString(1, iname);
			rs = prepSt.executeQuery();
			while (rs.next()){
				item.setIid(rs.getInt("iid"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		if (item.isNull()) return 0;
		else return item.getIid();
	}
	
	public static int insertItem(Item item,int uid,String info){
		if (item.getLevel() == 0) return insertRootItemByItem(item, uid, info);
		return insertItemByItem(item, uid, info);
	}
	
	/**
	 * 插入一个新Item，相关信息通过item参数获取，首次的内容来自info字符串
	 * 首先插入新的Item，然后在vitems表中插入相关内容。
	 * 仅用于插入非根节点！
	 * @param item
	 * @param info
	 * @param uid
	 */
	private static int insertItemByItem(Item item,int uid,String info){
		//首先插入一个item
		int ret = 0;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		String sqlInsert = "insert into items(iname,level,parent,root) values(?,?,?,?)";
		try{
			prepSt = conn.prepareStatement(sqlInsert);
			prepSt.setString(1, item.getIname());
			prepSt.setInt(2, item.getLevel());
			prepSt.setInt(3, item.getParent());
			prepSt.setInt(4, item.getRoot());
			prepSt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
		//然后插入一个新的vitem
		prepSt = null;
		Item itemAdded = getItemByNameLevelRoot(item.getIname(),item.getLevel(),item.getRoot());
		ret = itemAdded.getIid();
		Timestamp time = new Timestamp(System.currentTimeMillis());
		sqlInsert = "insert into vitems(iid,uid,info,createtime) values(?,?,?,?)";
		try{
			prepSt = conn.prepareStatement(sqlInsert);
			prepSt.setInt(1, itemAdded.getIid());
			prepSt.setInt(2, uid);
			prepSt.setString(3, info);
			prepSt.setTimestamp(4, time);
			prepSt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
		//最后更新原来item表中的curvid值
		prepSt = null;
		String sqlUpdate = "update items set curvid = (select vid from vitems where uid = ? and createtime = ?) where iid = ?";
		try{
			prepSt = conn.prepareStatement(sqlUpdate);
			prepSt.setInt(1, uid);
			prepSt.setTimestamp(2, time);
			prepSt.setInt(3, itemAdded.getIid());
			prepSt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}finally{
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		//返回成功的结果
		return ret;
	}
	
	/**
	 * 插入一个根Item，具体方法同非根方法insertItemByItem()
	 * @param item
	 * @param uid
	 * @param info
	 * @return boolean
	 */
	private static int insertRootItemByItem(Item item, int uid, String info){
		//首先插入一个item
		int ret = 0;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		String sqlInsert = "insert into items(iname,level) values(?,?)";
		try{
			prepSt = conn.prepareStatement(sqlInsert);
			prepSt.setString(1, item.getIname());
			prepSt.setInt(2, 0);
			prepSt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
		//然后插入一个新的vitem
		prepSt = null;
		int iid = getRootIidByName(item.getIname());
		ret = iid;
		Timestamp time = new Timestamp(System.currentTimeMillis());
		sqlInsert = "insert into vitems(iid,uid,info,createtime) values(?,?,?,?)";
		try{
			prepSt = conn.prepareStatement(sqlInsert);
			prepSt.setInt(1, iid);
			prepSt.setInt(2, uid);
			prepSt.setString(3, info);
			prepSt.setTimestamp(4, time);
			prepSt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
		
		//最后更新原来item表中的curvid值
		prepSt = null;
		String sqlUpdate = "update items set curvid = (select vid from vitems where uid = ? and createtime = ?) , root = ? , level = ? where iid = ?";
		try{
			prepSt = conn.prepareStatement(sqlUpdate);
			prepSt.setInt(1, uid);
			prepSt.setTimestamp(2, time);
			prepSt.setInt(3, iid);
			prepSt.setInt(4, 0);
			prepSt.setInt(5, iid);
			prepSt.executeUpdate();
		}catch(Exception e){
			System.out.println(e.toString());
			return 0;
		}finally{
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		//返回成功的结果
		return ret;
	}
	
	/**
	 * 检测iid标识的item是否为叶子节点
	 * @param iid
	 * @return boolean
	 */
	public static boolean HasChild(int iid){
		boolean ret = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String sqlQuery = "select * from items where parent = ? ";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, iid);
			rs = prepSt.executeQuery();
			while (rs.next()){
				ret = true;
			}
		}catch(Exception e){
			e.printStackTrace();
			return ret;
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		return ret;
	}
	
	/**
	 * 检测item是否为叶子节点
	 * @param item
	 * @return boolean
	 */
	public static boolean HasChild(Item item){
		if (item.isNull()) return false;
		boolean ret = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String sqlQuery = "select * from items where parent = (select iid from items where iname = ? adn root = ? and level = ?)";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setString(1, item.getIname());
			prepSt.setInt(2, item.getRoot());
			prepSt.setInt(3, item.getLevel());
			rs = prepSt.executeQuery();
			while (rs.next()){
				ret = true;
			}
		}catch(Exception e){
			e.printStackTrace();
			return ret;
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		return ret;
	}
	
	/**
	 * 获取item的全部孩子节点 
	 * @param item
	 * @return
	 */
	public static List<Item> getChildren(Item item){
		if (HasChild(item.getIid()) == false) return null;

		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		List<Item> list = new ArrayList<Item>();
		String sqlQuery = "select * from items where parent = ?";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, item.getIid());
			rs = prepSt.executeQuery();
			while (rs.next()){
				Item childitem = new Item();
				childitem.setIid(rs.getInt("iid"));
				childitem.setIname(rs.getString("iname"));
				childitem.setCurvid(rs.getInt("curvid"));
				childitem.setLevel(rs.getInt("level"));
				childitem.setParent(rs.getInt("parent"));
				childitem.setRoot(rs.getInt("root"));
				list.add(childitem);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		return list;
	}
	
	/**
	 * 获取某item的最后被审核结果的更新时间，以字符串形式返回
	 * @param item
	 * @return String
	 */
	public static String getUpdateTime(Item item){
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		Timestamp time = null;
		String sqlQuery = "select createtime from vitems where vid = (select curvid from items where iid = ?)";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, item.getIid());
			rs = prepSt.executeQuery();
			while(rs.next()){
				time = rs.getTimestamp("createtime");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeResultSet(rs);
		}
		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String ret = sdf.format(time);
		return ret;
	}
	
	public static List<VItem> getEditHistory(int iid){
		List<VItem> list = new ArrayList<VItem>();
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String sqlQuery = "select * from vitems where iid = ?";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, iid);
			rs = prepSt.executeQuery();
			while(rs.next()){
				VItem vitem = new VItem();
				vitem.setCreatetime(rs.getTimestamp("createtime"));
				vitem.setInfo(rs.getString("info"));
				vitem.setIid(iid);
				vitem.setVid(rs.getInt("vid"));
				vitem.setUid(rs.getInt("uid"));
				list.add(vitem);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeResultSet(rs);
		}
		
		return list;
	}
	
	/**
	 * 得到item当前的vitem中的正文内容
	 * @param item
	 * @return String
	 */
	public static String getInfoByItem(Item item){
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String info = "";
		String sqlQuery = "select info from vitems where vid = (select curvid from items where iid = ?)";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, item.getIid());
			rs = prepSt.executeQuery();
			while(rs.next()){
				info = rs.getString("info");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeResultSet(rs);
		}
		
		info = MarkBiz.MarkToHtml(info);
		return info;
	}


	/**
	 * 返回的应为root item!!!!
	 */
	@Override
	public List<Item> SolidSearch(String keyWord) {
		List<Item> list = new ArrayList<Item>();
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String sqlQuery = "select * from items where iname ~ ?";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setString(1, keyWord);
			rs = prepSt.executeQuery();
			while(rs.next()){
				Item item = new Item();
				item.setIname(rs.getString("iname"));
				item.setIid(rs.getInt("iid"));
				item.setCurvid(rs.getInt("curvid"));
				item.setParent(0);
				item.setLevel(rs.getInt("level"));
				item.setRoot(rs.getInt("root"));
				list.add(item);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeResultSet(rs);
		}
		
		return list;
	}

	/**
	 * 返回全部参与的RootItem
	 */
	@Override
	public List<Item> UserSearch(int uid) {
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		List<Item> list = new ArrayList<Item>();
		PreparedStatement prepSt = null;
		ResultSet rs = null;
		String sqlQuery = "select distinct * from items where iid in (select root from items where iid in (select iid from vitems where uid = ?)) order by curvid desc";
		try{
			prepSt = conn.prepareStatement(sqlQuery);
			prepSt.setInt(1, uid);
			rs = prepSt.executeQuery();
			while(rs.next()){
				Item item = new Item();
				item.setIid(rs.getInt("iid"));
				item.setIname(rs.getString("iname"));
				item.setCurvid(rs.getInt("curvid"));
				item.setLevel(rs.getInt("level"));
				item.setParent(rs.getInt("parent"));
				item.setRoot(rs.getInt("root"));
				list.add(item);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBBiz.closeResultSet(rs);
			DBBiz.closePreparedStatement(prepSt);
			DBBiz.closeConnection(conn);
		}
		
		return list;
	}
	
	public static  boolean updateItemByItem(Item item, String info, int uid){
		boolean flag = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		
		//向vitems表中插入新的更新内容
		Timestamp time = new Timestamp(System.currentTimeMillis());
		String sqlInsert = "insert into vitems(iid,uid,info,createtime) values(?,?,?,?)";
		try{
			prepSt = conn.prepareStatement(sqlInsert);
			prepSt.setInt(1, item.getIid());
			prepSt.setInt(2, uid);
			prepSt.setString(3, info);
			prepSt.setTimestamp(4, time);
			prepSt.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
		}
		
/*		//更改items表中的curvid

		*/
		return flag;
	}
	
	public static boolean updateCurvid(int vid){
		boolean flag = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		prepSt = null;
		String sqlUpdate = "update items set curvid = ? where iid = (select iid from vitems where vid = ?)";
		try{
			prepSt = conn.prepareStatement(sqlUpdate);
			prepSt.setInt(1, vid);
			prepSt.setInt(2, vid);
			prepSt.execute();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
		}
		
		return flag;
	}
	
	public static boolean deleteVitems(int vid){
		boolean flag = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		prepSt = null;
		String sqlUpdate = "delete from vitems where vid = ?";
		try{
			prepSt = conn.prepareStatement(sqlUpdate);
			prepSt.setInt(1, vid);
			prepSt.execute();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
		}
		
		return flag;
	}
	
	public static boolean deleteItems(int iid){
		boolean flag = false;
		DBConn dbc = new DBConn();
		Connection conn = dbc.getConn();
		PreparedStatement prepSt = null;
		prepSt = null;
		String sqlUpdate = "delete from items where iid = ?";
		try{
			prepSt = conn.prepareStatement(sqlUpdate);
			prepSt.setInt(1, iid);
			prepSt.execute();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			DBBiz.closeConnection(conn);
			DBBiz.closePreparedStatement(prepSt);
		}
		
		return flag;
	}
	
	public static List<Item> genList(Item item){
		List<Item> l = null;
		l = ItemTree.PreOrder(item);
		return l;
	}

}
